
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 10/13/2012 15:41:12
-- Generated from EDMX file: C:\Users\ZiyaadM\SVNProjects\AGRTraining\AGRTraining\za\co\agrtraining\model\AGRModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [AGR_DB];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_CertificateFactStudentCertificate]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactStudentCertificates] DROP CONSTRAINT [FK_CertificateFactStudentCertificate];
GO
IF OBJECT_ID(N'[dbo].[FK_ClientProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Projects] DROP CONSTRAINT [FK_ClientProject];
GO
IF OBJECT_ID(N'[dbo].[FK_Course]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactCourseSubjectTrainers] DROP CONSTRAINT [FK_Course];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseStudentCourse]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactStudentCourses] DROP CONSTRAINT [FK_CourseStudentCourse];
GO
IF OBJECT_ID(N'[dbo].[FK_Employee]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactCourseSubjectTrainers] DROP CONSTRAINT [FK_Employee];
GO
IF OBJECT_ID(N'[dbo].[FK_EmployeeFactTrainerQualification]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactTrainerQualifications] DROP CONSTRAINT [FK_EmployeeFactTrainerQualification];
GO
IF OBJECT_ID(N'[dbo].[FK_EmployeeUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [FK_EmployeeUser];
GO
IF OBJECT_ID(N'[dbo].[FK_PlacementPlacementHistory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactPlacementHistories] DROP CONSTRAINT [FK_PlacementPlacementHistory];
GO
IF OBJECT_ID(N'[dbo].[FK_ProjectPlacement]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Placements1] DROP CONSTRAINT [FK_ProjectPlacement];
GO
IF OBJECT_ID(N'[dbo].[FK_QualificationTrainerQualification]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactTrainerQualifications] DROP CONSTRAINT [FK_QualificationTrainerQualification];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentCourseProgressFactStudentCourse]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactStudentCourses] DROP CONSTRAINT [FK_StudentCourseProgressFactStudentCourse];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentCourseProgressStudentSubjectProgress]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[StudentSubjectProgresses] DROP CONSTRAINT [FK_StudentCourseProgressStudentSubjectProgress];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentFactStudentCertificate]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactStudentCertificates] DROP CONSTRAINT [FK_StudentFactStudentCertificate];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentPlacementHistory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactPlacementHistories] DROP CONSTRAINT [FK_StudentPlacementHistory];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentStudentCourse]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactStudentCourses] DROP CONSTRAINT [FK_StudentStudentCourse];
GO
IF OBJECT_ID(N'[dbo].[FK_Subject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FactCourseSubjectTrainers] DROP CONSTRAINT [FK_Subject];
GO
IF OBJECT_ID(N'[dbo].[FK_UserRoleUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Users] DROP CONSTRAINT [FK_UserRoleUser];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[AttendanceCertificates]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AttendanceCertificates];
GO
IF OBJECT_ID(N'[dbo].[Clients]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Clients];
GO
IF OBJECT_ID(N'[dbo].[Courses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Courses];
GO
IF OBJECT_ID(N'[dbo].[Employees]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Employees];
GO
IF OBJECT_ID(N'[dbo].[FactCourseSubjectTrainers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[FactCourseSubjectTrainers];
GO
IF OBJECT_ID(N'[dbo].[FactPlacementHistories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[FactPlacementHistories];
GO
IF OBJECT_ID(N'[dbo].[FactStudentCertificates]', 'U') IS NOT NULL
    DROP TABLE [dbo].[FactStudentCertificates];
GO
IF OBJECT_ID(N'[dbo].[FactStudentCourses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[FactStudentCourses];
GO
IF OBJECT_ID(N'[dbo].[FactTrainerQualifications]', 'U') IS NOT NULL
    DROP TABLE [dbo].[FactTrainerQualifications];
GO
IF OBJECT_ID(N'[dbo].[Placements1]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Placements1];
GO
IF OBJECT_ID(N'[dbo].[Projects]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Projects];
GO
IF OBJECT_ID(N'[dbo].[Qualifications]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Qualifications];
GO
IF OBJECT_ID(N'[dbo].[StudentCourseProgresses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StudentCourseProgresses];
GO
IF OBJECT_ID(N'[dbo].[Students]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Students];
GO
IF OBJECT_ID(N'[dbo].[StudentSubjectProgresses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StudentSubjectProgresses];
GO
IF OBJECT_ID(N'[dbo].[Subjects]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Subjects];
GO
IF OBJECT_ID(N'[dbo].[TempAuditTables]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TempAuditTables];
GO
IF OBJECT_ID(N'[dbo].[UserRoles]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserRoles];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Students'
CREATE TABLE [dbo].[Students] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [StudentCode] nvarchar(max)  NOT NULL,
    [StudentIDNumber] nvarchar(max)  NOT NULL,
    [StudentFirstName] nvarchar(max)  NULL,
    [StudentLastName] nvarchar(max)  NULL,
    [StudentGender] nvarchar(max)  NULL,
    [StudentPhysicalAddress] nvarchar(max)  NULL,
    [StudentPostalAddress] nvarchar(max)  NULL,
    [StudentProvince] nvarchar(max)  NULL,
    [StudentNationality] nvarchar(max)  NULL,
    [StudentLanguage] nvarchar(max)  NULL,
    [StudentEquity] nvarchar(max)  NULL,
    [StudentDisability] nvarchar(max)  NULL,
    [StudentResidentialStatus] nvarchar(max)  NULL,
    [StudentSocioStatus] nvarchar(max)  NULL,
    [StudentFirstLanguage] nvarchar(max)  NULL,
    [StudentSecondLanguage] nvarchar(max)  NULL
);
GO

-- Creating table 'Courses'
CREATE TABLE [dbo].[Courses] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [CourseCode] nvarchar(max)  NOT NULL,
    [CourseAccreditationNumber] nvarchar(max)  NOT NULL,
    [CourseName] nvarchar(max)  NOT NULL,
    [CourseDuration] nvarchar(max)  NOT NULL,
    [CourseCategory] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'FactStudentCourses'
CREATE TABLE [dbo].[FactStudentCourses] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [StudentCode] nvarchar(max)  NOT NULL,
    [CourseCode] nvarchar(max)  NOT NULL,
    [DateRegistered] datetime  NULL,
    [Student_Id] int  NULL,
    [Course_Id] int  NULL,
    [StudentCourseProgress_Id] int NULL
);
GO

-- Creating table 'FactCourseSubjectTrainers'
CREATE TABLE [dbo].[FactCourseSubjectTrainers] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [CourseCode] nvarchar(max)  NOT NULL,
    [SubjectCode] nvarchar(max)  NOT NULL,
    [EmployeeCode] nvarchar(max)  NOT NULL,
    [Employee_Id] int  NULL,
    [Course_Id] int NULL,
    [Subject_Id] int  NULL
);
GO

-- Creating table 'Employees'
CREATE TABLE [dbo].[Employees] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [EmployeeCode] nvarchar(max)  NOT NULL,
    [EmployeeFirstName] nvarchar(max)  NULL,
    [EmployeeLastName] nvarchar(max)  NULL,
    [EmployeeGender] tinyint  NULL,
    [EmployeePhysicalAddress] nvarchar(max)  NULL,
    [EmployeePostalAddress] nvarchar(max)  NULL,
    [EmployeeType] nvarchar(max)  NULL,
    [IsTrainer] tinyint  NULL,
    [TrainerSpecialization] nvarchar(max)  NULL,
    [Users_Id] int  NOT NULL
);
GO

-- Creating table 'Placements1'
CREATE TABLE [dbo].[Placements1] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [PlacementCode] nvarchar(max)  NOT NULL,
    [PlacementType] nvarchar(max)  NULL,
    [ProjectCode] nvarchar(max)  NOT NULL,
    [Project_Id] int  NOT NULL
);
GO

-- Creating table 'FactPlacementHistories'
CREATE TABLE [dbo].[FactPlacementHistories] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [StudentCode] nvarchar(max)  NOT NULL,
    [PlacementCode] nvarchar(max)  NOT NULL,
    [Placement_Id] int  NOT NULL,
    [Student_Id] int  NOT NULL
);
GO

-- Creating table 'Clients'
CREATE TABLE [dbo].[Clients] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ClientCode] nvarchar(max)  NOT NULL,
    [ClientName] nvarchar(max)  NULL,
    [ClientDescription] nvarchar(max)  NOT NULL,
    [ClientEmail] nvarchar(max)  NULL,
    [ClientTelNumber] nvarchar(max)  NULL,
    [ClientFaxNumber] nvarchar(max)  NULL,
    [ClientPhysicalAddress] nvarchar(max)  NULL,
    [ClientPostalAddress] nvarchar(max)  NULL
);
GO

-- Creating table 'Projects'
CREATE TABLE [dbo].[Projects] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ProjectCode] nvarchar(max)  NOT NULL,
    [ProjectName] nvarchar(max)  NULL,
    [ProjectContactPerson] nvarchar(max)  NULL,
    [ProjectContactNumber] nvarchar(max)  NULL,
    [ProjectType] nvarchar(max)  NULL,
    [ProjectLocation] nvarchar(max)  NULL,
    [ProjectStartDate] datetime  NULL,
    [ProjectExpectedEndDate] datetime  NULL,
    [Property] nvarchar(max)  NULL,
    [ClientCode] nvarchar(max)  NULL,
    [ContractorCode] nvarchar(max)  NULL,
    [Client_Id] int  NOT NULL
);
GO

-- Creating table 'AttendanceCertificates'
CREATE TABLE [dbo].[AttendanceCertificates] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [CertificateCode] nvarchar(max)  NOT NULL,
    [CertificateType] nvarchar(max)  NULL,
    [CertificateDescription] nvarchar(max)  NULL
);
GO

-- Creating table 'UserRoles'
CREATE TABLE [dbo].[UserRoles] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserRoleCode] nvarchar(max)  NOT NULL,
    [UserRoleType] nvarchar(max)  NULL
);
GO

-- Creating table 'Subjects'
CREATE TABLE [dbo].[Subjects] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [SubjectCode] nvarchar(max)  NOT NULL,
    [SubjectType] nvarchar(max)  NULL,
    [SubjectTitle] nvarchar(max)  NULL,
    [SubjectLevel] nvarchar(max)  NULL,
    [SubjectCredits] int  NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserName] nvarchar(max)  NULL,
    [UserPassword] nvarchar(max)  NULL,
    [UserRoleCode] nvarchar(max)  NULL,
    [UserRole_Id] int  NOT NULL
);
GO

-- Creating table 'StudentCourseProgresses'
CREATE TABLE [dbo].[StudentCourseProgresses] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [StudentCode] nvarchar(max)  NOT NULL,
    [CourseCode] nvarchar(max)  NOT NULL,
    [CreditsMaximum] int  NULL,
    [CreditsAchieved] int  NULL,
    [Competent] tinyint  NULL,
    [DateAchieved] datetime  NULL,
    [CompletionTime] nvarchar(max)  NULL,
    [StudentProgressCode] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Qualifications'
CREATE TABLE [dbo].[Qualifications] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [QualificationCode] nvarchar(max)  NOT NULL,
    [QualificationType] nvarchar(max)  NULL,
    [QualificationDescription] nvarchar(max)  NULL
);
GO

-- Creating table 'FactTrainerQualifications'
CREATE TABLE [dbo].[FactTrainerQualifications] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [EmployeeCode] nvarchar(max)  NOT NULL,
    [QualificationCode] nvarchar(max)  NOT NULL,
    [EmployeeId] int  NOT NULL,
    [Qualification_Id] int  NOT NULL
);
GO

-- Creating table 'TempAuditTables'
CREATE TABLE [dbo].[TempAuditTables] (
    [Id] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'StudentSubjectProgresses'
CREATE TABLE [dbo].[StudentSubjectProgresses] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [StudentCode] nvarchar(max)  NOT NULL,
    [CourseCode] nvarchar(max)  NOT NULL,
    [SubjectCode] nvarchar(max)  NOT NULL,
    [AchievableCredits] nvarchar(max)  NULL,
    [Completed] tinyint  NULL,
    [StudentCourseProgress_Id] int  NOT NULL
);
GO

-- Creating table 'FactStudentCertificates'
CREATE TABLE [dbo].[FactStudentCertificates] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [StudentCode] nvarchar(max)  NOT NULL,
    [CertificateCode] nvarchar(max)  NOT NULL,
    [Student_Id] int  NOT NULL,
    [Certificate_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Students'
ALTER TABLE [dbo].[Students]
ADD CONSTRAINT [PK_Students]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [PK_Courses]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'FactStudentCourses'
ALTER TABLE [dbo].[FactStudentCourses]
ADD CONSTRAINT [PK_FactStudentCourses]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'FactCourseSubjectTrainers'
ALTER TABLE [dbo].[FactCourseSubjectTrainers]
ADD CONSTRAINT [PK_FactCourseSubjectTrainers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Employees'
ALTER TABLE [dbo].[Employees]
ADD CONSTRAINT [PK_Employees]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Placements1'
ALTER TABLE [dbo].[Placements1]
ADD CONSTRAINT [PK_Placements1]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'FactPlacementHistories'
ALTER TABLE [dbo].[FactPlacementHistories]
ADD CONSTRAINT [PK_FactPlacementHistories]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Clients'
ALTER TABLE [dbo].[Clients]
ADD CONSTRAINT [PK_Clients]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Projects'
ALTER TABLE [dbo].[Projects]
ADD CONSTRAINT [PK_Projects]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AttendanceCertificates'
ALTER TABLE [dbo].[AttendanceCertificates]
ADD CONSTRAINT [PK_AttendanceCertificates]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserRoles'
ALTER TABLE [dbo].[UserRoles]
ADD CONSTRAINT [PK_UserRoles]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Subjects'
ALTER TABLE [dbo].[Subjects]
ADD CONSTRAINT [PK_Subjects]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'StudentCourseProgresses'
ALTER TABLE [dbo].[StudentCourseProgresses]
ADD CONSTRAINT [PK_StudentCourseProgresses]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Qualifications'
ALTER TABLE [dbo].[Qualifications]
ADD CONSTRAINT [PK_Qualifications]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'FactTrainerQualifications'
ALTER TABLE [dbo].[FactTrainerQualifications]
ADD CONSTRAINT [PK_FactTrainerQualifications]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TempAuditTables'
ALTER TABLE [dbo].[TempAuditTables]
ADD CONSTRAINT [PK_TempAuditTables]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'StudentSubjectProgresses'
ALTER TABLE [dbo].[StudentSubjectProgresses]
ADD CONSTRAINT [PK_StudentSubjectProgresses]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'FactStudentCertificates'
ALTER TABLE [dbo].[FactStudentCertificates]
ADD CONSTRAINT [PK_FactStudentCertificates]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [UserRole_Id] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [FK_UserRoleUser]
    FOREIGN KEY ([UserRole_Id])
    REFERENCES [dbo].[UserRoles]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserRoleUser'
CREATE INDEX [IX_FK_UserRoleUser]
ON [dbo].[Users]
    ([UserRole_Id]);
GO

-- Creating foreign key on [Users_Id] in table 'Employees'
ALTER TABLE [dbo].[Employees]
ADD CONSTRAINT [FK_EmployeeUser]
    FOREIGN KEY ([Users_Id])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeUser'
CREATE INDEX [IX_FK_EmployeeUser]
ON [dbo].[Employees]
    ([Users_Id]);
GO

-- Creating foreign key on [Client_Id] in table 'Projects'
ALTER TABLE [dbo].[Projects]
ADD CONSTRAINT [FK_ClientProject]
    FOREIGN KEY ([Client_Id])
    REFERENCES [dbo].[Clients]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClientProject'
CREATE INDEX [IX_FK_ClientProject]
ON [dbo].[Projects]
    ([Client_Id]);
GO

-- Creating foreign key on [Project_Id] in table 'Placements1'
ALTER TABLE [dbo].[Placements1]
ADD CONSTRAINT [FK_ProjectPlacement]
    FOREIGN KEY ([Project_Id])
    REFERENCES [dbo].[Projects]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectPlacement'
CREATE INDEX [IX_FK_ProjectPlacement]
ON [dbo].[Placements1]
    ([Project_Id]);
GO

-- Creating foreign key on [Placement_Id] in table 'FactPlacementHistories'
ALTER TABLE [dbo].[FactPlacementHistories]
ADD CONSTRAINT [FK_PlacementPlacementHistory]
    FOREIGN KEY ([Placement_Id])
    REFERENCES [dbo].[Placements1]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PlacementPlacementHistory'
CREATE INDEX [IX_FK_PlacementPlacementHistory]
ON [dbo].[FactPlacementHistories]
    ([Placement_Id]);
GO

-- Creating foreign key on [Student_Id] in table 'FactPlacementHistories'
ALTER TABLE [dbo].[FactPlacementHistories]
ADD CONSTRAINT [FK_StudentPlacementHistory]
    FOREIGN KEY ([Student_Id])
    REFERENCES [dbo].[Students]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentPlacementHistory'
CREATE INDEX [IX_FK_StudentPlacementHistory]
ON [dbo].[FactPlacementHistories]
    ([Student_Id]);
GO

-- Creating foreign key on [Student_Id] in table 'FactStudentCourses'
ALTER TABLE [dbo].[FactStudentCourses]
ADD CONSTRAINT [FK_StudentStudentCourse]
    FOREIGN KEY ([Student_Id])
    REFERENCES [dbo].[Students]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentStudentCourse'
CREATE INDEX [IX_FK_StudentStudentCourse]
ON [dbo].[FactStudentCourses]
    ([Student_Id]);
GO

-- Creating foreign key on [Course_Id] in table 'FactCourseSubjectTrainers'
ALTER TABLE [dbo].[FactCourseSubjectTrainers]
ADD CONSTRAINT [FK_CourseCourseSubject]
    FOREIGN KEY ([Course_Id])
    REFERENCES [dbo].[Courses]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseCourseSubject'
CREATE INDEX [IX_FK_CourseCourseSubject]
ON [dbo].[FactCourseSubjectTrainers]
    ([Course_Id]);
GO

-- Creating foreign key on [Subject_Id] in table 'FactCourseSubjectTrainers'
ALTER TABLE [dbo].[FactCourseSubjectTrainers]
ADD CONSTRAINT [FK_SubjectCourseSubject]
    FOREIGN KEY ([Subject_Id])
    REFERENCES [dbo].[Subjects]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SubjectCourseSubject'
CREATE INDEX [IX_FK_SubjectCourseSubject]
ON [dbo].[FactCourseSubjectTrainers]
    ([Subject_Id]);
GO

-- Creating foreign key on [Qualification_Id] in table 'FactTrainerQualifications'
ALTER TABLE [dbo].[FactTrainerQualifications]
ADD CONSTRAINT [FK_QualificationTrainerQualification]
    FOREIGN KEY ([Qualification_Id])
    REFERENCES [dbo].[Qualifications]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_QualificationTrainerQualification'
CREATE INDEX [IX_FK_QualificationTrainerQualification]
ON [dbo].[FactTrainerQualifications]
    ([Qualification_Id]);
GO

-- Creating foreign key on [Course_Id] in table 'FactStudentCourses'
ALTER TABLE [dbo].[FactStudentCourses]
ADD CONSTRAINT [FK_CourseStudentCourse]
    FOREIGN KEY ([Course_Id])
    REFERENCES [dbo].[Courses]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseStudentCourse'
CREATE INDEX [IX_FK_CourseStudentCourse]
ON [dbo].[FactStudentCourses]
    ([Course_Id]);
GO

-- Creating foreign key on [StudentCourseProgress_Id] in table 'StudentSubjectProgresses'
ALTER TABLE [dbo].[StudentSubjectProgresses]
ADD CONSTRAINT [FK_StudentCourseProgressStudentSubjectProgress]
    FOREIGN KEY ([StudentCourseProgress_Id])
    REFERENCES [dbo].[StudentCourseProgresses]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentCourseProgressStudentSubjectProgress'
CREATE INDEX [IX_FK_StudentCourseProgressStudentSubjectProgress]
ON [dbo].[StudentSubjectProgresses]
    ([StudentCourseProgress_Id]);
GO

-- Creating foreign key on [StudentCourseProgress_Id] in table 'FactStudentCourses'
ALTER TABLE [dbo].[FactStudentCourses]
ADD CONSTRAINT [FK_StudentCourseProgressFactStudentCourse]
    FOREIGN KEY ([StudentCourseProgress_Id])
    REFERENCES [dbo].[StudentCourseProgresses]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentCourseProgressFactStudentCourse'
CREATE INDEX [IX_FK_StudentCourseProgressFactStudentCourse]
ON [dbo].[FactStudentCourses]
    ([StudentCourseProgress_Id]);
GO

-- Creating foreign key on [Student_Id] in table 'FactStudentCertificates'
ALTER TABLE [dbo].[FactStudentCertificates]
ADD CONSTRAINT [FK_StudentFactStudentCertificate]
    FOREIGN KEY ([Student_Id])
    REFERENCES [dbo].[Students]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentFactStudentCertificate'
CREATE INDEX [IX_FK_StudentFactStudentCertificate]
ON [dbo].[FactStudentCertificates]
    ([Student_Id]);
GO

-- Creating foreign key on [Certificate_Id] in table 'FactStudentCertificates'
ALTER TABLE [dbo].[FactStudentCertificates]
ADD CONSTRAINT [FK_CertificateFactStudentCertificate]
    FOREIGN KEY ([Certificate_Id])
    REFERENCES [dbo].[AttendanceCertificates]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CertificateFactStudentCertificate'
CREATE INDEX [IX_FK_CertificateFactStudentCertificate]
ON [dbo].[FactStudentCertificates]
    ([Certificate_Id]);
GO

-- Creating foreign key on [EmployeeId] in table 'FactTrainerQualifications'
ALTER TABLE [dbo].[FactTrainerQualifications]
ADD CONSTRAINT [FK_EmployeeFactTrainerQualification]
    FOREIGN KEY ([EmployeeId])
    REFERENCES [dbo].[Employees]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeFactTrainerQualification'
CREATE INDEX [IX_FK_EmployeeFactTrainerQualification]
ON [dbo].[FactTrainerQualifications]
    ([EmployeeId]);
GO

-- Creating foreign key on [Employee_Id] in table 'FactCourseSubjectTrainers'
ALTER TABLE [dbo].[FactCourseSubjectTrainers]
ADD CONSTRAINT [FK_EmployeeFactCourseSubjectTrainer]
    FOREIGN KEY ([Employee_Id])
    REFERENCES [dbo].[Employees]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeFactCourseSubjectTrainer'
CREATE INDEX [IX_FK_EmployeeFactCourseSubjectTrainer]
ON [dbo].[FactCourseSubjectTrainers]
    ([Employee_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------